package com.fl.nj.util;

import com.fl.nj.pool.ConnectionPoolManager;
import com.fl.nj.rule.DataFetchRule;

import java.sql.*;
import java.util.*;

/**
 * 数据库操作工具类
 * 提供数据库查询、插入等操作的方法
 */
public class DBUtil {

    /**
     * 执行查询操作
     * @param poolId 连接池ID
     * @param sql SQL查询语句
     * @return 查询结果列表
     * @throws SQLException 数据库操作异常
     */
    public static List<Map<String, Object>> executeQuery(String poolId, String sql) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Map<String, Object>> resultList = new ArrayList<>();

        try {
            conn = ConnectionPoolManager.getConnection(poolId);
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                resultList.add(row);
            }
        } finally {
            closeResources(rs, stmt, conn);
        }

        return resultList;
    }

    /**
     * 根据规则获取数据
     * @param poolId 连接池ID
     * @param rule 数据获取规则
     * @return 查询结果列表
     * @throws SQLException 数据库操作异常
     */
    public static List<Map<String, Object>> fetchDataByRule(String poolId, DataFetchRule rule) throws SQLException {
        String sql = rule.buildSql();
        return executeQuery(poolId, sql);
    }

    /**
     * 执行插入操作
     * @param poolId 连接池ID
     * @param sql SQL插入语句
     * @param params 参数列表
     * @return 影响的行数
     * @throws SQLException 数据库操作异常
     */
    public static int executeInsert(String poolId, String sql, List<Object> params) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        int affectedRows = 0;

        try {
            conn = ConnectionPoolManager.getConnection(poolId);
            stmt = conn.prepareStatement(sql);

            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    stmt.setObject(i + 1, params.get(i));
                }
            }

            affectedRows = stmt.executeUpdate();
        } finally {
            closeResources(null, stmt, conn);
        }

        return affectedRows;
    }

    /**
     * 批量插入数据
     * @param poolId 连接池ID
     * @param sql SQL插入语句
     * @param dataList 数据列表
     * @return 影响的行数
     * @throws SQLException 数据库操作异常
     */
    public static int[] batchInsert(String poolId, String sql, List<List<Object>> dataList) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        int[] affectedRows = null;

        try {
            conn = ConnectionPoolManager.getConnection(poolId);
            conn.setAutoCommit(false);
            stmt = conn.prepareStatement(sql);

            for (List<Object> rowData : dataList) {
                for (int i = 0; i < rowData.size(); i++) {
                    stmt.setObject(i + 1, rowData.get(i));
                }
                stmt.addBatch();
            }

            affectedRows = stmt.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            if (conn != null) {
                conn.rollback();
            }
            throw e;
        } finally {
            if (conn != null) {
                conn.setAutoCommit(true);
            }
            closeResources(null, stmt, conn);
        }

        return affectedRows;
    }

    /**
     * 关闭数据库资源
     * @param rs 结果集
     * @param stmt 语句对象
     * @param conn 连接对象
     */
    private static void closeResources(ResultSet rs, Statement stmt, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}